use master
go
create database OnlinePostOffice
go
--drop database OnlinePostOffice

use OnlinePostOffice
go

--drop table Service
create table Service
(
	ServiceId int identity primary key ,
	Name varchar(50) ,
	Fee money ,
	IMG varchar(50)
)

--drop table Branch

go

create table Customer
(
	CustID int identity primary key ,
	[Name] varchar(50) ,
	Address varchar(50) ,
	Tel varchar(30) ,
	Email varchar(30)
)

go

create table Area
(
	Pincode int identity primary key ,
	Location varchar(50)
)

go

create table Branch
(
	BranchId int identity primary key ,
	Name varchar(50) ,
	Address varchar(50) ,
	PinCode int references Area(Pincode)
)

go

create table Feetype
(
	FeeTypeID int identity primary key ,
	FirstArea int references Area(PinCode) ,
	SecondArea int references Area(PinCode) ,
	Fee money
)

go

create table Delivery
(
	DeliveryNo int identity primary key ,
	Weight float ,
	DateOfPosting datetime ,
	ServiceID int references Service(ServiceID) ,
	DestinationAddress varchar(50) ,
	CustID int references Customer(CustID) ,
	FeeTypeID int references Feetype(FeeTypeID) ,
	Status bit,
	ReceiveDate datetime
)

go

--drop table Feedback
create table Feedback
(
	DeliveryNo int references Delivery(DeliveryNo),
	CustID int references Customer(CustID),
	[Content] varchar(50) ,
	isReplied bit ,
	primary key (DeliveryNo , CustID)
)

go

create table Employee
(
	Username varchar(50) primary key ,
	Password varchar(50) ,
	Fullname varchar(50) ,
	Birthdate datetime ,
	Gender bit ,
	Email varchar(50) ,
	Address varchar(50) ,
	Tel varchar(30)
)

--insert data

insert into Service values ('Courier', 20000,'~/img/services/courier.png')
insert into Service values ('Parcel', 30000,'~/img/services/parcel.png')
insert into Service values ('Money', 10000,'~/img/services/money.png')
insert into Service values ('Speedcost', 25000,'~/img/services/speedcost.png')
insert into Service values ('VPP', 15000,'~/img/services/vpp.png')

insert into Customer values ('Jun Pyo', 'Korea', '0123456789', 'junpyo@email.com')
insert into Customer values ('Jan Di', 'Korea', '1234567890', 'jandi@email.com')
insert into Customer values ('Ji Hoo', 'Korea', '9876543210', 'jihoo@email.com')

insert into Area values ('Delhi')
insert into Area values ('Assam')
insert into Area values ('Goa')

insert into Branch values ('Delhi G.P.O', 'North Delhi', 1)
insert into Branch values ('Delhi High Court S.O', 'Central Delhi', 1)
insert into Branch values ('Adabari B.O', 'Nalbari', 2)
insert into Branch values ('Amguri S.O', 'Sibsagar', 2)
insert into Branch values ('Gogol S.O', 'South Goa', 3)

insert into Feetype values (1, 2, 20000)
insert into Feetype values (3, 2, 40000)
insert into Feetype values (2, 1, 15000)
insert into Feetype values (1, 3, 15000)
insert into Feetype values (3, 1, 15000)
insert into Feetype values (2, 3, 35000)

insert into Delivery values (10.5, '1/1/2010', 3, 'Delhi', 2, 3, 0, NULL)
insert into Delivery values (25, '2/15/2010', 1, 'Assam', 1, 1, 1, '2/22/2010')
insert into Delivery values (8, '2/22/2010', 4, 'Assam', 2, 2, 1, '2/25/2010')
insert into Delivery values (100, '4/1/2010', 2, 'Goa', 3, 4, 0, NULL)
insert into Delivery values (30, '3/11/2010', 5, 'Delhi', 1, 5, 1, '4/11/2010')
insert into Delivery values (22, '3/22/2010', 1, 'Goa', 3, 3, 0, NULL)
insert into Delivery values (99, '4/12/2010', 3, 'Delhi', 2, 5, 1, '4/15/2010 15:10:00')

insert into Feedback values (1, 2, 'the delivery has not been sent yet', 1)
insert into Feedback values (4, 3, 'wrong destination address of the delivery', 0)